/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');


// Schema 结构
var ItbBuyTmplModel = function(obj){
    ItbBaseModel.call(this,obj);

  // 以下为表定义数据，需要的字段就填入
  //`buy_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '购买id',
  //`buy_no` varchar(50) NOT NULL DEFAULT '' COMMENT '自定义购买编号',
  //`type` int(3) NOT NULL COMMENT '模版类型(大分类): 1海报 2多页 3ppt',
  //`tmpl_id` bigint(20) DEFAULT NULL COMMENT '模版id',
  //`user_id` bigint(20) DEFAULT NULL COMMENT '购买用户id',
  //`state` int(3) NOT NULL DEFAULT '0' COMMENT '购买状态:11已支付、12支付失败、60过期失效',
  //`valid_time_start` datetime NOT NULL COMMENT '有效期起始日时',
  //`valid_time_end` datetime NOT NULL COMMENT '有效期结束日时',
  //`pay_platform` int(3) NOT NULL DEFAULT '0' COMMENT '支付平台: 10微信, 20支付宝',
  //`total_fee` bigint(12) NOT NULL DEFAULT '0' COMMENT '购买总金额（单位为rmb分）= tmpl_price - coupon_fee',
  //`tmpl_price` bigint(12) NOT NULL DEFAULT '0' COMMENT '模版售价',
  //`coupon_fee` bigint(12) NOT NULL DEFAULT '0' COMMENT '子项：优惠费（单位为rmb分）',
  //`coupon_id` bigint(20) unsigned DEFAULT NULL COMMENT '优惠券id',
  //`desc` varchar(200) DEFAULT NULL COMMENT '描述',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`update_by` varchar(50) NOT NULL,
  //`del_flg` int(1) NOT NULL DEFAULT '0',

};
ItbBuyTmplModel.prototype = new ItbBaseModel();

// 取得表名
ItbBuyTmplModel.prototype.getTableName = function(){
    return 'itb_buy_tmpl';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbBuyTmplModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'buy_id='+this.buy_id;
}

//----------------------------------------------------------------
// 确认用户是否已购买模板
ItbBuyTmplModel.prototype.chkBuyTmplInfo = function(userId,type,tmplId,callback){
    var THIS = this;
    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql = '';
        if(type==1 || type==2) {
            sql = ' SELECT  t.*, d.design_name AS tmpl_name, '+
                ' case when t.valid_time_end >= sysdate() then 1 else 0 end AS valid '+
            ' FROM `itb_buy_tmpl` t,`tg_design_doc` d '+
            ' WHERE t.del_flg=0 AND t.type IN (1,2) AND t.state IN (11) '+
            ' AND t.user_id='+userId + ' AND t.tmpl_id='+tmplId+
            ' AND t.tmpl_id=d.design_id AND d.del_flg=0 AND d.publish=1 ';

        } else if(type==3){
            sql = ' SELECT  t.*, d.tmpl_name, '+
                ' case when t.valid_time_end >= sysdate() then 1 else 0 end AS valid '+
                ' FROM `itb_buy_tmpl` t,`itb_template` d '+
                ' WHERE t.del_flg=0 AND t.type IN (3) AND t.state IN (11) '+
                ' AND t.user_id='+userId + ' AND t.tmpl_id='+tmplId+
                ' AND t.tmpl_id=d.tmpl_id AND d.del_flg=0 AND d.publish=1 ';

        } else {
            // TODO
            sql = ' SELECT  t.*, NULL AS tmpl_name, '+
                ' case when t.valid_time_end >= sysdate() then 1 else 0 end AS valid '+
            ' FROM `itb_buy_tmpl` '+
            ' WHERE t.del_flg=0 AND t.type IN ('+type+') AND t.state IN (11) '+
            ' AND t.user_id='+userId + ' AND t.tmpl_id='+tmplId;
        }

        sql += ' ORDER BY valid DESC, t.create_time DESC ';

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}

//----------------------------------------------------------------
// 获取用户购买模版列表
ItbBuyTmplModel.prototype.getBuyTmplListByUser = function(userId,skipCnt,pageSize,callback){
    var THIS = this;
    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql = " SELECT tt.* FROM ( "+
            " SELECT ret.*, DATE_FORMAT(ret.create_time, '%Y-%m-%d') AS create_date, "+
            " DATE_FORMAT(ret.valid_time_end, '%Y-%m-%d') AS valid_date_end, "+
            " CASE WHEN (ret.valid_time_end is null OR ret.valid_time_end='0000-00-00 00:00:00' OR ret.valid_time_end>=sysdate()) "+
            "      THEN 0 ELSE 1 END AS expired "+
            " FROM ( "+
            "   (select t.*, d.design_name AS tmpl_name, p.thumb AS tmpl_thumb "+
            "   from `itb_buy_tmpl` t,`tg_design_doc` d, tg_design_page p "+
            "   where t.del_flg=0 and t.type in (1,2) and t.state in (11) and t.user_id="+userId+
            "   and d.design_id=t.tmpl_id and d.publish=1 and d.del_flg=0 "+
            "   and p.design_id=d.design_id and p.page_index=0 and p.del_flg=0 ) "+
            "   union all"+
            "   (select t2.*, d2.tmpl_name, p2.thumb_s AS tmpl_thumb "+
            "   from `itb_buy_tmpl` t2,`itb_template` d2, itb_tmpl_page p2 "+
            "   where t2.del_flg=0 and t2.type in (3) and t2.state in (11) and t2.user_id="+userId+
            "   and d2.tmpl_id=t2.tmpl_id and d2.publish=1 and d2.del_flg=0 "+
            "   and p2.tmpl_id=d2.tmpl_id and p2.page_index=0 and p2.del_flg=0 ) "+
            " ) ret "+
            " ORDER BY ret.create_time DESC ) tt ";

        if(typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
}


// 导出
module.exports = ItbBuyTmplModel;